package com.group_g.hkr;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JPanel;

public class DB_Connector {

	//Random connection variables
	private Connection con;
	private Statement st;
	private ResultSet rs;
	private PreparedStatement ps = null;
	public DB_Connector(){
		try{
			Class.forName("com.mysql.jdbc.Driver");	
			//Connect to mysql directory, root connection, password
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/onlineshop","root","s1m0ne");
			st = con.createStatement();
		}catch(Exception ex){
			
		System.out.println("Error is " + ex);	
			
		}		
	}
	
public int checkQuantity(String name){
	int a = 1;
	 try {
		ps = con.prepareStatement("SELECT amount FROM product where name = '"+name+"';");
		while (rs.next()) {
			rs = ps.executeQuery();
			a = rs.getInt("amount");
			System.out.println(a);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}

	System.out.println(a);
	return a;
}
	
public void deleteProduct(){
	 try {
		ps = con.prepareStatement("DELETE FROM product where name = '0';");
		ps.executeUpdate();
	} catch (SQLException e) {
	}

}
	
public boolean checkUser(String uName,String uPassword){
		
		try{
			
			String query1 = "select Name from user where Name = ?";
			String query2 = "select Password from user where Name = ?";
			ps = con.prepareStatement(query1);
			ps.setString(1, uName);
			rs = ps.executeQuery();

			while(rs.next()){
									
					ps = con.prepareStatement(query2);
					ps.setString(1, uName);
					rs = ps.executeQuery();
					while(rs.next()){
						if(uPassword.equals(rs.getString("Password"))){
							System.out.println("Welcome "+uName);
							return true ;
						}
					}
				
			}
			
		}catch(Exception ex){
		}
		
		return false;
		
}

public Object[] SearchProduct(String name){
	Object[] product = new Object[10];
	int id = 0;
	String name1 = null;
	String price = null;
	String company = null;
	String amount = null;
	String image = null;
	int x = 0;
	int y = 10;
	int width = 400;
	int height = 50;
	
	try{
		String query = "select * from product where Name = ? ";
		ps = con.prepareStatement(query);
		ps.setString(1, name);
		rs = ps.executeQuery();
		
		while(rs.next()){
			id = rs.getInt("id");
			name1 = rs.getString("name");
			price = rs.getString("price");
			company = rs.getString("company");
			amount = rs.getString("amount");
			image = rs.getString("image");
			y+=50;
			
			product[0] = id;
			product[1] = name1;
			product[2] = price;
			product[3] = company;
			product[4] = amount;
			product[5] = image;
			product[6] = x;
			product[7] = y;
			product[8] = width;
			product[9] = height;
		}
		
	}catch(Exception ex){
		
		
	}
	
	return product;
	
}

public void UpdateProduct(String name, int inventory){
	try{
		//String query = "update product set amount = '"+inventory+"'  where name = '"+name+"'";
		String query = "update product set amount = ?  where name = ?";
		ps = con.prepareStatement(query);
		ps.setInt(1, inventory);
		ps.setString(2, name);
		rs = ps.executeQuery();
		
		/*PreparedStatement update = prepareStatement("update`products` set `amount` = '"+inventory+"'  where `name` = '"+name+"'");
		   ResultSet updateEXP_done = update.executeUpdate();
		   */
		
	}catch(Exception ex){
		
		
	}
}

public void executeSQL(String preparedSql, String pName) throws Exception {
    ps = con.prepareStatement(preparedSql);
    if (pName != null) {
          ps.setString(1, pName);
    }
    ps.executeUpdate();
    
}	
	

public void showMenue(){
		
		try{
			
			String query = "select * from product";
			rs = st.executeQuery(query);
			System.out.println(" Product Details ");
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int price = rs.getInt("price");
				String company = rs.getString("company");
				int amount = rs.getInt("amount");
				String image = rs.getString("image");
	
				
				System.out.println("ID:  " + id + "  Name:  "+ name + "  Price:  "+ price + "  company: " + company + "  amount:  "+ amount + "  image  " + image);
			
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
			//System.out.println(ex);
		}
		
	}

	
	
}